﻿using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using SmartRFIDSystem19.dao;

namespace SmartRFIDSystem19.dao
{
    public class LogDao
    {
        //查询当前账户的余额
        public static float GetBalance(string mfid)
        {
            Connection.Connect();
            string sqlStr = "select balance from account where mfid = @mfid";
            Connection.comm = new MySqlCommand(sqlStr, Connection.conn);
            Connection.comm.Parameters.Add(new MySqlParameter("@mfid", mfid));
            Connection.comm.ExecuteNonQuery();
            float balance = (float)Connection.comm.ExecuteScalar();
            return balance;
        }
        //充值
        public static void Recharge(string mfid, float amount, string remark)
        {
            Connection.Connect();

            float balance = GetBalance(mfid);

            //生成当前时间
            string date = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            string sqlStr = "insert into log (id,mfid,date,type,amount,balance,remark) values (@id,@mfid,@date,@type,@amount,@balance,@remark)";
            Connection.comm = new MySqlCommand(sqlStr, Connection.conn);
            Connection.comm.Parameters.Add(new MySqlParameter("@id", null));
            Connection.comm.Parameters.Add(new MySqlParameter("@mfid", mfid));
            Connection.comm.Parameters.Add(new MySqlParameter("@date", date));
            Connection.comm.Parameters.Add(new MySqlParameter("@type", "充值"));
            Connection.comm.Parameters.Add(new MySqlParameter("@amount", amount));
            Connection.comm.Parameters.Add(new MySqlParameter("@balance", balance + amount));
            Connection.comm.Parameters.Add(new MySqlParameter("@remark", remark.Equals("") ? "无" : remark));
            try
            {
                int r = Connection.comm.ExecuteNonQuery();
                if (r == 1)
                {
                    MessageBox.Show("充值成功");
                    return;
                }
                else
                {
                    MessageBox.Show("充值失败");
                    return;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("充值失败");
                return;
            }
            finally
            {

                if (Connection.conn != null)
                    Connection.conn.Close();
            }


        }
        //消费
        public static void Consume(string mfid, float amount, string remark)
        {
            Connection.Connect();

            float balance = GetBalance(mfid);
            //生成当前时间
            string date = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            string sqlStr = "insert into log (id,mfid,date,type,amount,balance,remark) values (@id,@mfid,@date,@type,@amount,@balance,@remark)";
            Connection.comm = new MySqlCommand(sqlStr, Connection.conn);
            Connection.comm.Parameters.Add(new MySqlParameter("@id", null));
            Connection.comm.Parameters.Add(new MySqlParameter("@mfid", mfid));
            Connection.comm.Parameters.Add(new MySqlParameter("@date", date));
            Connection.comm.Parameters.Add(new MySqlParameter("@type", "消费"));
            Connection.comm.Parameters.Add(new MySqlParameter("@amount", amount));
            Connection.comm.Parameters.Add(new MySqlParameter("@balance", balance - amount));
            Connection.comm.Parameters.Add(new MySqlParameter("@remark", remark.Equals("") ? "无" : remark));
            try
            {
                int r = Connection.comm.ExecuteNonQuery();
                if (r == 1)
                {
                    MessageBox.Show("消费成功");
                    return;
                }
                else
                {
                    MessageBox.Show("消费失败");
                    return;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("消费失败");
                return;
            }
            finally
            {
                if (Connection.conn != null)
                    Connection.conn.Close();
            }

        }
        //导出账单
        public static void ExportBill(string name ,string mfid)
        {
            Connection.Connect();
            //查询数据库中log表中的所有数据
            string sqlStr = "select * from log";
            //查询数据库中log表中mfid为mfid的数据
            if (!mfid.Equals(""))
            {
                sqlStr = "select * from log where mfid = '" + mfid + "'";
            }
            Connection.comm = new MySqlCommand(sqlStr, Connection.conn);
            MySqlDataReader reader = Connection.comm.ExecuteReader();
            //创建工作簿
            XSSFWorkbook workbook = new XSSFWorkbook();
            //创建sheet
            ISheet Sheet = workbook.CreateSheet("账单");
            //新建一行
            IRow row = Sheet.CreateRow(0);
            //新建列
            row.CreateCell(0).SetCellValue("操作编号");
            row.CreateCell(1).SetCellValue("姓名");
            row.CreateCell(2).SetCellValue("卡号");
            row.CreateCell(3).SetCellValue("操作时间");
            row.CreateCell(4).SetCellValue("操作类型");
            row.CreateCell(5).SetCellValue("操作金额");
            row.CreateCell(6).SetCellValue("余额");
            row.CreateCell(7).SetCellValue("备注");
            int i = 1;
            while (reader.Read())
            {
                row = Sheet.CreateRow(i);
                row.CreateCell(0).SetCellValue(reader.GetInt32("id"));
                row.CreateCell(1).SetCellValue(name);
                row.CreateCell(2).SetCellValue(reader.GetString("mfid"));
                row.CreateCell(3).SetCellValue(reader.GetString("date"));
                row.CreateCell(4).SetCellValue(reader.GetString("type"));
                row.CreateCell(5).SetCellValue(reader.GetInt32("amount"));
                row.CreateCell(6).SetCellValue(reader.GetInt32("balance"));
                row.CreateCell(7).SetCellValue(reader.GetString("remark"));
                i++;
            }
            //保存
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.Filter = "Microsoft Excel账单|*.xlsx";
            sfd.FileName = name==""?"操作数据":(name+"的账单");
            sfd.DefaultExt = "xlsx";
            sfd.AddExtension = true;
            if (sfd.ShowDialog() == DialogResult.OK)
            {
                string filePath = sfd.FileName;
                FileStream sw = File.Create(filePath);
                workbook.Write(sw);
                sw.Close();
                MessageBox.Show("导出账单成功！");
            }
            //关闭连接
            Connection.conn.Close();
            //MessageBox.Show("数据库连接关闭！");

        }
        public static void ExportBill()
        {
            ExportBill("","");
        }
    }
}
